import pandas, duckdb
from config import ini_op
from openpyxl import  load_workbook
import os
from utils.path_utils import get_project_root
from database.duckdb.duckdb_base import get_connection



# timenow = str(strftime("%Y%m%d%H%M%S"))
def run(dwbh, zbid, zb, kjnd, filepath, timenow):
  workbook = load_workbook(filepath, data_only=True)
  sheet = workbook.active
  kjnd = sheet['A2'].value[0:4]
  df1 = pandas.read_excel(filepath, skiprows=5, dtype=str)
  conn = get_connection()
  conn.register('temp_kmyeb_main', df1)
  zbnm = ini_op.getinivalue('zb_dict', 'zb' + zb)[0]
  conn.execute(f"""
    create or replace table t_kmyeb_main (科目编号 string,科目名称 string,期初方向 string,期初金额 string,借方 string,贷方 string,余额方向 string,余额金额 string,组织编号 string,组织名称 string,账簿编号 string,账簿名称 string);
    insert into t_kmyeb_main select * from temp_kmyeb_main;
    delete from t_kmyeb_main where 账簿名称 != '{zbnm}';
               """)

  conn.execute("""
               delete from T_API_KMNC where kjnd = ? and LEDGER = ? and ACCTITLECODE in (select 科目编号 from t_kmyeb_main group by 科目编号);
               """, [kjnd, zbid])
  # conn.execute("""
  #       create or replace table t_yl_kmyeb as
  #              select
  #               uuid() as id,
  #               '"""+dwbh+"""' as 单位编号,
  #               '"""+zb+"""' as 账簿编号,
  #               '"""+kjnd+"""' as 会计年度,
  #               组织名称 as 组织名称,
  #               科目编号,
  #               科目名称,
  #               期初方向,
  #               期初金额,
  #               借方 as 借方,
  #               贷方 as 贷方,
  #               余额方向 as 余额方向,
  #               余额金额 as 余额金额,
  #               '"""+timenow+"""' as 更新时间
  #               from t_kmyeb_main;
  #   """)
#   print(zb)
  conn.execute("""
    insert into T_API_KMNC (LEDGER, ACCTITLECODE, BALANCEDIR, BEGINBALANCEAMT, CURCUMDRAMT, CURCUMCRAMT, ENDBALANCEAMT, esun_api_update, kjnd)
              select
                '"""+zbid+"""',
                科目编号,
                case 期初方向
                when '平' then 0
                when '借' then 1
                when '贷' then 2
                end,
                CAST(REPLACE(期初金额, ',', '') AS DECIMAL(18, 2)),
                CAST(REPLACE(借方, ',', '') AS DECIMAL(18, 2)),
                CAST(REPLACE(贷方, ',', '') AS DECIMAL(18, 2)),
                CAST(REPLACE(余额金额, ',', '') AS DECIMAL(18, 2)),
                '"""+timenow+"""',
                '"""+kjnd+"""'
               from t_kmyeb_main;
    delete from T_API_KMNC where ACCTITLECODE = '合计';
  """)

  conn.close()



# run('03047', '01051507', '2024', r'D:\code\app_report_back20241201\backend\项目余额表.xlsx', timenow)